package org.caowei.samples.dao;



import org.caowei.samples.model.ExamScore;
import org.caowei.samples.vo.ExamScoreVO;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;


@Repository
public class ExamScoreDAO extends BaseHibernateDAO<ExamScore> implements IExamScoreDAO {

	//根据用户ID和科目ID读取成绩
	@Override
	public ExamScore getScore(String userId,Integer subId){
		String hql = " FROM ExamScore es where es.UserId = :userId and es.subjectId =:subjectId";
		return (ExamScore) this.createQuery(hql)
				.setString("userId", userId)
				.setInteger("subjectId", subId)
				.uniqueResult();
	}
	
	@Override
	public ExamScoreVO getScoreById(Integer scoreId){
		StringBuilder hql = new StringBuilder();
		hql.append(" select user.userName as userName,sub.subName as subName,es.examTime as examTime, es.score as score ");
		hql.append(" from ExamScore es, User user,Subject sub ");
		hql.append(" where es.UserId = user.userId ");
		hql.append(" and es.subjectId = sub.subId");
		hql.append(" and es.scoreId = :scoreId ");
		
		return (ExamScoreVO) this.createQuery(hql.toString())
				.setResultTransformer(Transformers.aliasToBean(ExamScoreVO.class))
				.setInteger("scoreId", scoreId)
				.uniqueResult();
	}

//	@Override
//	@SuppressWarnings("unchecked")
//	public List<ExamScoreVO> getAllScore(Map<String, String> conditionMap) {
//		StringBuilder hql = new StringBuilder();
//		hql.append(" select es.id as id, user.userId as userId, user.userName as userName, sub.subId as subjectId, sub.name as subjectName ");
//		hql.append(" from ExamScore es, ExamUser user, ExamSubject sub ");
//		hql.append(" where es.userId = user.userId ");
//		hql.append(" and es.subjectId = sub.subId ");
//		
//		//当条件为空时，相当于查询全部
//		if(conditionMap != null){
//			Iterator<Entry<String, String>> it = conditionMap.entrySet().iterator();
//			while(it.hasNext()){
//				Entry<String, String> currentCondition = it.next();
//				//当有指定类型的查询条件时，按条件查询
//				if ("subjectName".equals(currentCondition.getKey())){
//					hql.append(" and sub.name like '%" + currentCondition.getValue() + "%' ");
//				}else if ("userId".equals(currentCondition.getKey())){
//					hql.append(" and user.userId like '%" + currentCondition.getValue() + "%' ");
//				}else if ("userName".equals(currentCondition.getKey())){
//					hql.append(" and user.userName like '%" + currentCondition.getValue() + "%' ");
//				}else if ("examTime".equals(currentCondition.getKey())){
//					if(currentCondition.getValue() != null){
//						//未写完hql
//						hql.append(" and DATE_FORMAT(es.examTime,'%Y-%m-%d %H:%i') like '%" + currentCondition.getValue() + "%' ");
//					}
//				}
//			}
//		}
//		return  this.createQuery(hql.toString())
//				.setResultTransformer(Transformers.aliasToBean(ExamScoreVO.class))
//				.list();
//	}
	
}
